﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dunnice.BlogIndex.Entity;
using System.Data.SQLite;
using System.Reflection;
using System.Data;
using Dunnice.Utility;

namespace Dunnice.BlogIndex.DLL
{
    public class BlogDatabase
    {
        #region Singleton

        private string _dbFile;
        private object _asyncObj = new object();
        private static BlogDatabase _instance;
        public static BlogDatabase Instance
        {
            get
            {
                return _instance;
            }
        }

        static BlogDatabase()
        {
            _instance = new BlogDatabase();
        }

        private BlogDatabase()
        {
            _dbFile = AppDomain.CurrentDomain.BaseDirectory + "myblog.db";
        }

        #endregion

        /// <summary>
        /// 保存博客文章信息,包括正文.
        /// </summary>
        /// <param name="info"></param>
        public void SaveArticleInfo(BlogArticleInfo info)
        {
            lock (_asyncObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(System.Configuration.ConfigurationManager.ConnectionStrings["blogdb"].ConnectionString))
                {
                    conn.Open();

                    SQLiteCommand comm = conn.CreateCommand();

                    comm.CommandText = string.Format(@"SELECT COUNT(1) FROM  blog b2 
                        WHERE blog_user = '{0}' AND blog_server = '{1}'  AND b2.blog_url = '{2}' ",
                        info.AuthorName, info.ServerName, info.Url);

                    bool havaArticle = int.Parse(comm.ExecuteScalar().ToString()) > 0;

                    if (havaArticle)
                    {
                        // 更新文章内容.
                        comm.CommandText = @"UPDATE blog  SET content = @content 
                        WHERE  blog_user = @blog_user AND  blog_server = @blog_server AND title  = @title AND pubtime= @pubtime ";

                        comm.Parameters.Add(new SQLiteParameter("content", DbType.Object) { Value = info.Content });
                        comm.Parameters.Add(new SQLiteParameter("blog_user", DbType.String) { Value = info.AuthorName });
                        comm.Parameters.Add(new SQLiteParameter("blog_server", DbType.String) { Value = info.ServerName });
                        comm.Parameters.Add(new SQLiteParameter("title", DbType.String) { Value = info.Title });
                        comm.Parameters.Add(new SQLiteParameter("pubtime", DbType.String) { Value = info.PostTime });

                        comm.ExecuteNonQuery();
                    }
                    else
                    {
                        // 新增文章.
                        comm.CommandText = @"INSERT INTO blog 
                        (blog_user, blog_server,  title, blog_url,  cate ,id,read, pubtime,content ) 
                        VALUES(@blog_user, @blog_server, @title,  @blog_url, @cate, @id, @read, @pubtime,@content)";

                        comm.Parameters.Add(new SQLiteParameter("blog_user", DbType.String) { Value = info.AuthorName ?? string.Empty });
                        comm.Parameters.Add(new SQLiteParameter("blog_server", DbType.String) { Value = info.ServerName ?? string.Empty });
                        comm.Parameters.Add(new SQLiteParameter("title", DbType.String) { Value = info.Title ?? string.Empty });
                        comm.Parameters.Add(new SQLiteParameter("blog_url", DbType.String) { Value = info.Url ?? string.Empty });
                        comm.Parameters.Add(new SQLiteParameter("cate", DbType.String) { Value = "默认" });
                        comm.Parameters.Add(new SQLiteParameter("id", DbType.Int16) { Value = 0 });
                        comm.Parameters.Add(new SQLiteParameter("read", DbType.String) { Value = 0 });
                        comm.Parameters.Add(new SQLiteParameter("pubtime", DbType.String) { Value = info.PostTime });
                        comm.Parameters.Add(new SQLiteParameter("content", DbType.String) { Value = info.Content ?? string.Empty });
                    }
                    comm.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 获取指定博主的博客文章列表.
        /// </summary>
        /// <param name="author"></param>
        /// <param name="blogServer"></param>
        /// <param name="loadContent"></param>
        /// <returns></returns>
        public List<BlogArticleInfo> GetArticleInfos(BlogServerEnum blogServer, string author, bool loadContent = false)
        {
            BlogServerAttribute attr = ReflectHelper.GetEnumAttributeInfo<BlogServerEnum, BlogServerAttribute>(blogServer.ToString());
            if (attr == null)
            {
                throw new ArgumentException("该枚举没有应用BlogServerAttribute,不能在此使用", "blogServer");
            }

            List<BlogArticleInfo> lstRes = new List<BlogArticleInfo>();

            lock (_asyncObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(System.Configuration.ConfigurationManager.ConnectionStrings["blogdb"].ConnectionString))
                {
                    conn.Open();

                    SQLiteCommand comm = conn.CreateCommand();

                    string cont = " b2.blog_user, b2.bid, b2.blog_server, b2.title, b2.blog_url, b2.cate,   b2.id, b2.read, b2.pubtime";
                   
                    if (loadContent)
                    {
                        cont = " b2.blog_user, b2.bid, b2.blog_server, b2.title, b2.blog_url, b2.cate,   b2.id, b2.read, b2.pubtime,b2.content";
                    }

                    comm.CommandText = string.Format(@"SELECT {2}
                        FROM  blog b2  where blog_user = '{0}' and blog_server = '{1}' ",
                        author, attr.BlogServerName, cont);

                    IDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        BlogArticleInfo info = new BlogArticleInfo()
                          {
                              ServerName = dr["blog_server"].ToString(),
                              AuthorName = dr["blog_user"].ToString(),
                              DateString = dr["pubtime"].ToString(),
                              Title = dr["title"].ToString(),
                              Url = dr["blog_url"].ToString(),
                              Bid = long.Parse(dr["bid"].ToString())
                          };

                        if (loadContent)
                        {
                            info.Content = dr["content"].ToString();
                        }

                        lstRes.Add(info);
                    }
                }

            }

            return lstRes;
        }

        /// <summary>
        /// 存储博客作者信息.
        /// </summary>
        /// <param name="author"></param>
        public void SaveBlogAuthorInfo(BlogAuthorInfo author)
        {
            lock (_asyncObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(System.Configuration.ConfigurationManager.ConnectionStrings["blogdb"].ConnectionString))
                {
                    conn.Open();

                    SQLiteCommand comm = conn.CreateCommand();

                    string checkSql = string.Format("SELECT  COUNT(1)  FROM  user u WHERE user_name = '{0}' AND server_name= '{1}' ",
                        author.AuthorName, author.ServerName);
                    comm.CommandText = checkSql;

                    if (int.Parse(comm.ExecuteScalar().ToString()) > 0)
                    {
                        comm.CommandText = string.Format("DELETE   FROM  user   WHERE user_name = '{0}' AND server_name= '{1}' ",
                        author.AuthorName, author.ServerName);

                        comm.ExecuteNonQuery();
                    }

                    comm.CommandText = string.Format(@"INSERT INTO  user (user_name, server_name, user_url, gid, other_name, other_more )
                        VALUES('{0}','{1}','{2}','{3}','{4}','{5}')",
                        author.AuthorName, author.ServerName, author.BlogUrl, "1", author.Alias, string.Empty);

                    comm.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 获取所有博客作者信息.
        /// </summary>
        /// <returns></returns>
        public List<BlogAuthorInfo> GetBlogAuthorInfos()
        {
            List<BlogAuthorInfo> lstInfo = new List<BlogAuthorInfo>();

            lock (_asyncObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(System.Configuration.ConfigurationManager.ConnectionStrings["blogdb"].ConnectionString))
                {
                    conn.Open();

                    SQLiteCommand comm = conn.CreateCommand();
                    comm.CommandText = @"SELECT u.user_name , u.server_name , u.user_url , u.gid , u.other_name ,u.other_more , g.group_name 
FROM user u LEFT JOIN groupname g ON u.gid = g.[id] 
ORDER BY g.group_name , u.server_name , u.user_name ;";

                    IDataReader dr = comm.ExecuteReader();

                    while (dr.Read())
                    {
                        BlogAuthorInfo info = new BlogAuthorInfo()
                        {
                            BlogUrl = dr["user_url"].ToString(),
                            Alias = dr["other_name"].ToString(),
                            ServerName = dr["server_name"].ToString(),
                            AuthorName = dr["user_name"].ToString(),
                            GroupName = dr["group_name"].ToString(),
                            GroupId = int.Parse(dr["gid"].ToString()),
                            OtherInfo = dr["other_more"].ToString()
                        };

                        lstInfo.Add(info);
                    }
                }
            }

            return lstInfo;
        }
    }
}
